package org.gdstash.db;

import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DBMerchantTableAlloc {
    private static final String TABLE_NAME = "GDC_MERCHANTTABLE_ALLOC";
    private static final int ROW_TABLE_ID = 1;
    private static final int ROW_ITEM_ID = 2;
    private String tableID;
    private String itemID;

    public static void createTable() throws SQLException {
        final String dropTable = "DROP TABLE GDC_MERCHANTTABLE_ALLOC";
        final String createTable = "CREATE TABLE GDC_MERCHANTTABLE_ALLOC (TABLE_ID  VARCHAR(256) NOT NULL, ITEM_ID   VARCHAR(256) NOT NULL, PRIMARY KEY (TABLE_ID, ITEM_ID))";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            try (final Statement st = conn.createStatement()) {
                if (GDDBUtil.tableExists(conn, "GDC_MERCHANTTABLE_ALLOC")) {
                    st.execute(dropTable);
                }
                st.execute(createTable);
                st.close();
                conn.commit();
            } catch (SQLException ex) {
                conn.rollback();
                throw ex;
            } finally {
                conn.setAutoCommit(auto);
            }
        }
    }

    public static void delete(final Connection conn, final String tableID) throws SQLException {
        final String deleteEntry = "DELETE FROM GDC_MERCHANTTABLE_ALLOC WHERE TABLE_ID = ?";
        try (final PreparedStatement ps = conn.prepareStatement(deleteEntry)) {
            ps.setString(1, tableID);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static void insert(final Connection conn, final DBMerchantTable dbMerchantTable) throws SQLException {
        if (dbMerchantTable.getItemIDList() == null) {
            return;
        }
        if (dbMerchantTable.getItemIDList().isEmpty()) {
            return;
        }
        final String insert = "INSERT INTO GDC_MERCHANTTABLE_ALLOC VALUES (?,?)";
        try (final PreparedStatement ps = conn.prepareStatement(insert)) {
            for (final String item : dbMerchantTable.getItemIDList()) {
                ps.setString(1, dbMerchantTable.getMerchantTableID());
                ps.setString(2, item);
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static List<String> getByMerchantTableID(final String tableID) {
        List<String> itemIDs = null;
        final String command = "SELECT * FROM GDC_MERCHANTTABLE_ALLOC WHERE TABLE_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, tableID);
            try (final ResultSet rs = ps.executeQuery()) {
                itemIDs = wrap(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {tableID, "GDC_MERCHANTTABLE_ALLOC"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return itemIDs;
    }

    public static String getTableIDByItemID(final String itemID) {
        List<String> list = null;
        String tableID = null;
        final String command = "SELECT * FROM GDC_MERCHANTTABLE_ALLOC WHERE ITEM_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, itemID);
            try (final ResultSet rs = ps.executeQuery()) {
                list = wrapTableIDs(rs);
                if (list.isEmpty()) {
                    tableID = null;
                } else {
                    tableID = list.get(0);
                }
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {itemID, "GDC_MERCHANTTABLE_ALLOC"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return tableID;
    }

    private static List<String> wrap(final ResultSet rs) throws SQLException {
        final LinkedList<String> list = new LinkedList<String>();
        final boolean found = false;
        while (rs.next()) {
            final String itemID = rs.getString(2);
            list.add(itemID);
        }
        return list;
    }

    private static List<String> wrapTableIDs(final ResultSet rs) throws SQLException {
        final LinkedList<String> list = new LinkedList<String>();
        final boolean found = false;
        while (rs.next()) {
            final String tableID = rs.getString(1);
            list.add(tableID);
        }
        return list;
    }
}
